﻿<%@ WebHandler Language="C#" Class="RbApsLimitedCapacityHandler" %>

//Description:热表有限产能排产
//Copyright (c) : 通力凯顿（北京）系统集成有限公司
//Writer:zhangrj
//create Date:2024-07-23
using System;
using System.Web;
using BusinessRule.DTO;
using BusinessRule.Business;
using BusinessRule.IBusiness;
using BusinessRule.BusinessFactory;
using Newtonsoft.Json;
using System.Collections.Generic;
using System.Web.SessionState;
using System.Data;
using System.IO;
using System.Text;
using System.Linq;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using DatabaseAccess.DBUtility;
using System.Collections;

public class RbApsLimitedCapacityHandler : IHttpHandler, IRequiresSessionState
{
    private uLMSCommonIBusiness m_CommonObj = uMESBusinessFactory.instance.GetuLMSCommonObject();
    private RbApsLimitedCapacityBusiness rbApsObj = new RbApsLimitedCapacityBusiness();
    private LimitedCapacityBusiness m_LMObj = new LimitedCapacityBusiness();
    string fileBasePath = System.Configuration.ConfigurationManager.AppSettings["ImportPath"].ToString();//导入临时路径
    string rbFactoryID = System.Configuration.ConfigurationManager.AppSettings["RBFactoryID"].ToString(); //热表ID
    CurrentUserInfo m_UserInfo;
    public void ProcessRequest(HttpContext context)
    {
        try
        {
            var action = context.Request["action"].ToString();
            m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
            if (m_UserInfo == null)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "登录过期" }));
                return;
            }
            switch (action)
            {
                /*-----------------------
                 * 工步排产
                 * -----------------------*/
                case "InitRbApsLimitPageData": //初始化热表滚动制造计划页面数据
                    InitRbApsLimitPageData(context);
                    break;
                case "GetApsPlanContainerData": //获取可排产批次数据
                    GetApsPlanContainerData(context);
                    break;
                case "CalcContainerApsPriority": //计算排产池批次的优先级（顺序号）
                    CalcContainerApsPriority(context);
                    break;
                case "SaveManualStartDate": //保存手动指定日期
                    SaveManualStartDate(context);
                    break;
                case "CalcContainerApsPlan": //排产计算
                    CalcContainerApsPlan(context);
                    break;
                case "GetContainerApsDetailGantt": //获取批次排产明细甘特图数据
                    GetContainerApsDetailGantt(context);
                    break;
                case "GetContainerApsData": //获取批次排产信息，以及工步排产明细
                    GetContainerApsData(context);
                    break;
                case "ExportContainerApsLogInfo": //导出排产池批次的排产日志
                    ExportContainerApsLogInfo(context);
                    break;
                /*-----------------------
                 * 生产准备报表
                 * -----------------------*/
                case "GetToolProductPrepareData": //查询工装工具生产准备数据
                    GetToolProductPrepareData(context);
                    break;
                case "ExportToolProductPrepareData": //导出工装工具生产准备数据
                    ExportToolProductPrepareData(context);
                    break;
                case "GetMaterialProductPrepareData": //查询主辅材生产准备数据
                    GetMaterialProductPrepareData(context);
                    break;
                case "ExportMaterialProductPrepareData": //导出主辅材生产准备数据
                    ExportMaterialProductPrepareData(context);
                    break;
                case "GetResourceAbnormalStateData": //查询设备异常数据
                    GetResourceAbnormalStateData(context);
                    break;
                case "GetLinkWorkflowInfo": //获取第二层的工艺（跳转到工艺工步期量维护）
                    GetLinkWorkflowInfo(context);
                    break;
                default:
                    context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "未知操作" }));
                    break;
            }
        }
        catch (Exception e)
        {
            uMESProcessResult result = new uMESProcessResult()
            {
                Result = false,
                Message = e.Message
            };
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }

    /// <summary>
    /// 获取第二层的工艺
    /// </summary>
    /// <param name="context"></param>
    private void GetLinkWorkflowInfo(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true, ReturnData = "" };
        string workflowID = context.Request["workflowID"].ToString();
        string sql = string.Format(@"SELECT w.workflowid,w.productid FROM workflowstep ws 
                    LEFT JOIN workflow wl ON wl.workflowid = ws.workflowid 
                    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
                    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
                    INNER JOIN workflow w ON w.process_no = s.commonsubworkflow
                    WHERE w.workflowid = '{0}'", workflowID);
        if (DatabaseHelper.GetSingle(sql) != null)
        {
            sql = string.Format(@"SELECT w.productid,pb.productname,wb.workflowname FROM workflow w
                LEFT JOIN workflowbase wb ON wb.workflowbaseid = w.workflowbaseid
                LEFT JOIN product p ON p.productid = w.productid
                LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
                WHERE w.workflowid = '{0}'");
            DataTable dtWf = DatabaseHelper.Query_ReturnDatTable(sql);
            Dictionary<string, string> dic = new Dictionary<string, string>();
            dic.Add("workflowid", workflowID);
            dic.Add("productid", dtWf.Rows[0]["productid"].ToString());
            dic.Add("productname", dtWf.Rows[0]["productname"].ToString());
            dic.Add("workflowname", dtWf.Rows[0]["workflowname"].ToString());
            result.ReturnData = dic;
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    /// <summary>
    /// 查询设备异常数据
    /// </summary>
    /// <param name="context"></param>
    public void GetResourceAbnormalStateData(HttpContext context)
    {
        Dictionary<string, string> dic = JsonConvert.DeserializeObject<Dictionary<string, string>>(context.Request["params"].ToString());
        uMESPagingDataDTO pageDto = new uMESPagingDataDTO();
        pageDto.CurrentPageIndex = int.Parse(dic["pageIndex"]);
        pageDto.PageSize = int.Parse(dic["pageSize"]);
        pageDto.strSQL = GetResourceAbnormalStateDataSql(dic);
        uMESProcessResult result = DatabaseHelper.RunProcedureForPage(pageDto);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    /// <summary>
    /// 获取主辅材生产准备数据SQL
    /// </summary>
    /// <param name="dic"></param>
    /// <returns></returns>
    private string GetResourceAbnormalStateDataSql(Dictionary<string, string> dic)
    {
        StringBuilder sql = new StringBuilder();
        sql.AppendLine(@"SELECT f.factoryname,cci.fromworkcentername workcentername,cci.mfgordername,cci.productname,cci.containername,cci.familylistnames,
       DECODE(cci.containertype,7,'单件加工',2,'批量加工',3,'带子序号') containertype,fc.containername facontainername,
       DECODE(cci.containerstatus,1,'在制',2,'关闭') containerstatus,cci.workflowstepname,
       cci.workflowname || ':' || cci.workflowrevision workflowinfo,cci.qty containerqty,m.plannedstartdate mfgstartdate,
       cci.plannedstartdate containerstartdate,m.plannedcompletiondate mfgcompletedate,cci.plannedcompletiondate containercompletedate,
       s.stepno,cci.ExpectMaterialDate,s.description stepInfoDesc,
       ad.starttime,ad.endtime,nvl(rd.resourcename,wk.workstationname) resourcename,nvl(rd.description,wk.workstationname) resourcedesc
FROM apsplaninfo ai 
LEFT JOIN containercurrentinfo cci ON cci.containerid=ai.containerid
LEFT JOIN containersubcontainer csc ON csc.subcontainerid = cci.containerid --查询主制批次
LEFT JOIN container fc ON fc.containerid = csc.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN mfgorder m on m.mfgorderid = cci.mfgorderid
LEFT JOIN apsplandetail2 ad ON ad.apsplaninfoid = ai.apsplaninfoid AND ad.isuse=0
LEFT JOIN stepinfo s ON s.stepinfoid = ad.stepinfoid
LEFT JOIN resourcedef rd ON rd.resourceid = ad.resourceid
LEFT JOIN Workstation wk ON wk.workstationid=ad.resourceid
WHERE ai.isuse = 0 AND NVL(ad.status,'0') != '2'");
        if (dic.ContainsKey("MainFactoryID") && string.IsNullOrWhiteSpace(dic["MainFactoryID"]))
        {
            sql.AppendFormat(" AND fc.originalfactoryid = '{0}'", dic["MainFactoryID"]);
        }
        if (dic.ContainsKey("MfgOrderName") && string.IsNullOrWhiteSpace(dic["MfgOrderName"]))
        {
            sql.AppendFormat(" AND cci.mfgordername = '{0}'", dic["MfgOrderName"]);
        }
        if (dic.ContainsKey("ProductName") && string.IsNullOrWhiteSpace(dic["ProductName"]))
        {
            sql.AppendFormat(" AND cci.productname LIKE '%{0}%'", dic["ProductName"]);
        }
        if (dic.ContainsKey("ContainerName") && string.IsNullOrWhiteSpace(dic["ContainerName"]))
        {
            sql.AppendFormat(" AND cci.ContainerName LIKE '%{0}%'", dic["ContainerName"]);
        }
        if (dic.ContainsKey("StepInfoNo") && string.IsNullOrWhiteSpace(dic["StepInfoNo"]))
        {
            sql.AppendFormat(" AND s.stepno = '{0}'", dic["StepInfoNo"]);
        }
        if (dic.ContainsKey("ApsPlanStartDate1") && string.IsNullOrWhiteSpace(dic["ApsPlanStartDate1"]))
        {
            sql.AppendFormat(" AND ad.starttime >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", dic["ApsPlanStartDate1"]);
        }
        if (dic.ContainsKey("ApsPlanStartDate2") && string.IsNullOrWhiteSpace(dic["ApsPlanStartDate2"]))
        {
            sql.AppendFormat(" AND ad.starttime <= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", dic["ApsPlanStartDate2"]);
        }
        sql.AppendLine(" ORDER BY cci.containername,s.stepseq");
        return sql.ToString();
    }

    /// <summary>
    /// 导出主辅材生产准备数据
    /// </summary>
    /// <param name="context"></param>
    public void ExportMaterialProductPrepareData(HttpContext context)
    {
        Dictionary<string, string> dic = JsonConvert.DeserializeObject<Dictionary<string, string>>(context.Request["params"].ToString());
        DataTable dtRes = DatabaseHelper.Query_ReturnDatTable(GetMaterialProductPrepareDataSql(dic));
        foreach (DataColumn item in dtRes.Columns)
        {
            switch (item.ColumnName.ToUpper())
            {
                case "MFGORDERNAME":
                    item.ColumnName = "订单号"; break;
                case "CONTAINERNAME":
                    item.ColumnName = "批次号"; break;
                case "FACONTAINERNAME":
                    item.ColumnName = "主制批次"; break;
                case "WORKFLOWINFO":
                    item.ColumnName = "工艺信息"; break;
                case "CONTAINERQTY":
                    item.ColumnName = "在制数"; break;
                case "STEPNO":
                    item.ColumnName = "工步号"; break;
                case "STEPINFODESC":
                    item.ColumnName = "工步名"; break;
                case "STARTTIME":
                    item.ColumnName = "计划开始日期"; break;
                case "ENDTIME":
                    item.ColumnName = "计划结束日期"; break;
                case "AUXMATERIALNAME":
                    item.ColumnName = "辅材编号"; break;
                case "AUXMATERIALDESC":
                    item.ColumnName = "辅材名"; break;
                case "NEEDQTY":
                    item.ColumnName = "需求数量"; break;
                default:
                    break;
            }
        }
        DataTable dtExData = dtRes.AsDataView().ToTable(false, "订单号", "批次号", "主制批次", "工艺信息", "在制数", "工步号", "计划开始日期", "计划结束日期", "辅材编号", "辅材名", "需求数量");
        byte[] bytes = uLMS_CommonFunction.DataTableExportToExcel(dtExData);
        context.Response.Clear();
        context.Response.Buffer = true;
        context.Response.ClearHeaders();
        context.Response.ClearContent();
        context.Response.ContentType = "application/vnd.ms-excel";
        context.Response.AddHeader("fileDownload", "1");
        context.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}{1}.xls", context.Server.UrlEncode("工步工装工具生产准备导出记录"), DateTime.Now.ToString("yyyyMMddHHmmssffff")));
        context.Response.AddHeader("Content-Length", bytes.Length.ToString());
        context.Response.AddHeader("Content-Type", "UTF-8");
        context.Response.Charset = "UTF-8";
        context.Response.ContentEncoding = Encoding.UTF8;
        context.Response.BinaryWrite(bytes);
        context.Response.Flush();
        context.Response.Close();
    }

    /// <summary>
    /// 查询主辅材生产准备数据
    /// </summary>
    /// <param name="context"></param>
    public void GetMaterialProductPrepareData(HttpContext context)
    {
        Dictionary<string, string> dic = JsonConvert.DeserializeObject<Dictionary<string, string>>(context.Request["params"].ToString());
        uMESPagingDataDTO pageDto = new uMESPagingDataDTO();
        pageDto.CurrentPageIndex = int.Parse(dic["pageIndex"]);
        pageDto.PageSize = int.Parse(dic["pageSize"]);
        pageDto.strSQL = GetMaterialProductPrepareDataSql(dic);
        uMESProcessResult result = DatabaseHelper.RunProcedureForPage(pageDto);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    /// <summary>
    /// 获取主辅材生产准备数据SQL
    /// </summary>
    /// <param name="dic"></param>
    /// <returns></returns>
    private string GetMaterialProductPrepareDataSql(Dictionary<string, string> dic)
    {
        StringBuilder sql = new StringBuilder();
        sql.AppendLine(@"SELECT f.factoryname,cci.fromworkcentername workcentername,cci.mfgordername,cci.productname,cci.containername,cci.familylistnames,
       DECODE(cci.containertype,7,'单件加工',2,'批量加工',3,'带子序号') containertype,fc.containername facontainername,
       DECODE(cci.containerstatus,1,'在制',2,'关闭') containerstatus,cci.workflowstepname,
       cci.workflowname || ':' || cci.workflowrevision workflowinfo,cci.qty containerqty,m.plannedstartdate mfgstartdate,
       cci.plannedstartdate containerstartdate,m.plannedcompletiondate mfgcompletedate,cci.plannedcompletiondate containercompletedate,
       s.stepno,cci.ExpectMaterialDate,s.description stepInfoDesc,
       ad.starttime,ad.endtime,tl.auxmaterialname,tl.description auxmaterialdesc,tl.drawingno,tl.qty needqty,
         nvl(rd.resourcename,wk.workstationname) resourcename,nvl(rd.description,wk.workstationname) resourcedescription
FROM apsplaninfo ai 
LEFT JOIN containercurrentinfo cci ON cci.containerid=ai.containerid
LEFT JOIN containersubcontainer csc ON csc.subcontainerid = cci.containerid --查询主制批次
LEFT JOIN container fc ON fc.containerid = csc.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN mfgorder m on m.mfgorderid = cci.mfgorderid
LEFT JOIN apsplandetail2 ad ON ad.apsplaninfoid = ai.apsplaninfoid AND ad.isuse=0
LEFT JOIN stepinfo s ON s.stepinfoid = ad.stepinfoid
LEFT JOIN (
        SELECT  SUM(t.qty) qty,aux.auxmaterialname,aux.description,aux.vendormodel drawingno,rt.resourcetypename,t.stepinfoid
        FROM stepinfotools t
        LEFT JOIN stepinfo sio ON sio.stepinfoid = t.stepinfoid
        LEFT JOIN auxmaterial aux ON aux.auxmaterialid = t.tools
        LEFT JOIN resourcetype rt ON rt.resourcetypeid = aux.resourcetypeid
        WHERE aux.auxmaterialid IS NOT NULL 
        GROUP BY aux.auxmaterialname,aux.description,aux.vendormodel,rt.resourcetypename,t.stepinfoid
)tl ON tl.stepinfoid = s.stepinfoid
LEFT JOIN resourcedef rd ON rd.resourceid = ad.resourceid
LEFT JOIN Workstation wk ON wk.workstationid=ad.resourceid
WHERE ai.isuse = 0 AND NVL(tl.qty, 0) > 0 AND ad.status != '2'");
        if (dic.ContainsKey("MainFactoryID") && string.IsNullOrWhiteSpace(dic["MainFactoryID"]))
        {
            sql.AppendFormat(" AND fc.originalfactoryid = '{0}'", dic["MainFactoryID"]);
        }
        if (dic.ContainsKey("MfgOrderName") && string.IsNullOrWhiteSpace(dic["MfgOrderName"]))
        {
            sql.AppendFormat(" AND cci.mfgordername = '{0}'", dic["MfgOrderName"]);
        }
        if (dic.ContainsKey("ProductName") && string.IsNullOrWhiteSpace(dic["ProductName"]))
        {
            sql.AppendFormat(" AND cci.productname LIKE '%{0}%'", dic["ProductName"]);
        }
        if (dic.ContainsKey("ContainerName") && string.IsNullOrWhiteSpace(dic["ContainerName"]))
        {
            sql.AppendFormat(" AND cci.ContainerName LIKE '%{0}%'", dic["ContainerName"]);
        }
        if (dic.ContainsKey("StepInfoNo") && string.IsNullOrWhiteSpace(dic["StepInfoNo"]))
        {
            sql.AppendFormat(" AND s.stepno = '{0}'", dic["StepInfoNo"]);
        }
        if (dic.ContainsKey("ApsPlanStartDate1") && string.IsNullOrWhiteSpace(dic["ApsPlanStartDate1"]))
        {
            sql.AppendFormat(" AND ad.starttime >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", dic["ApsPlanStartDate1"]);
        }
        if (dic.ContainsKey("ApsPlanStartDate2") && string.IsNullOrWhiteSpace(dic["ApsPlanStartDate2"]))
        {
            sql.AppendFormat(" AND ad.starttime <= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", dic["ApsPlanStartDate2"]);
        }
        sql.AppendLine(" ORDER BY cci.containername,s.stepseq");
        return sql.ToString();
    }

    /// <summary>
    /// 导出工装工具生产准备数据
    /// </summary>
    /// <param name="context"></param>
    public void ExportToolProductPrepareData(HttpContext context)
    {
        Dictionary<string, string> dic = JsonConvert.DeserializeObject<Dictionary<string, string>>(context.Request["params"].ToString());
        DataTable dtRes = DatabaseHelper.Query_ReturnDatTable(GetToolProductPrepareDataSql(dic));
        foreach (DataColumn item in dtRes.Columns)
        {
            switch (item.ColumnName.ToUpper())
            {
                case "MFGORDERNAME":
                    item.ColumnName = "订单号"; break;
                case "CONTAINERNAME":
                    item.ColumnName = "批次号"; break;
                case "FACONTAINERNAME":
                    item.ColumnName = "主制批次"; break;
                case "WORKFLOWINFO":
                    item.ColumnName = "工艺信息"; break;
                case "CONTAINERQTY":
                    item.ColumnName = "在制数"; break;
                case "STEPNO":
                    item.ColumnName = "工步号"; break;
                case "STEPINFODESC":
                    item.ColumnName = "工步名"; break;
                case "STARTTIME":
                    item.ColumnName = "计划开始日期"; break;
                case "ENDTIME":
                    item.ColumnName = "计划结束日期"; break;
                case "TOOLNAME":
                    item.ColumnName = "工具编号"; break;
                case "TOOLDESC":
                    item.ColumnName = "工具名"; break;
                case "NEEDQTY":
                    item.ColumnName = "需求数量"; break;
                default:
                    break;
            }
        }
        DataTable dtExData = dtRes.AsDataView().ToTable(false, "订单号", "批次号", "主制批次", "工艺信息", "在制数", "工步号", "计划开始日期", "计划结束日期", "工具编号", "工具名", "需求数量");
        byte[] bytes = uLMS_CommonFunction.DataTableExportToExcel(dtExData);
        context.Response.Clear();
        context.Response.Buffer = true;
        context.Response.ClearHeaders();
        context.Response.ClearContent();
        context.Response.ContentType = "application/vnd.ms-excel";
        context.Response.AddHeader("fileDownload", "1");
        context.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}{1}.xls", context.Server.UrlEncode("工步工装工具生产准备导出记录"), DateTime.Now.ToString("yyyyMMddHHmmssffff")));
        context.Response.AddHeader("Content-Length", bytes.Length.ToString());
        context.Response.AddHeader("Content-Type", "UTF-8");
        context.Response.Charset = "UTF-8";
        context.Response.ContentEncoding = Encoding.UTF8;
        context.Response.BinaryWrite(bytes);
        context.Response.Flush();
        context.Response.Close();
    }

    /// <summary>
    /// 查询工装工具生产准备数据
    /// </summary>
    /// <param name="context"></param>
    public void GetToolProductPrepareData(HttpContext context)
    {
        Dictionary<string, string> dic = JsonConvert.DeserializeObject<Dictionary<string, string>>(context.Request["params"].ToString());
        uMESPagingDataDTO pageDto = new uMESPagingDataDTO();
        pageDto.CurrentPageIndex = int.Parse(dic["pageIndex"]);
        pageDto.PageSize = int.Parse(dic["pageSize"]);
        pageDto.strSQL = GetToolProductPrepareDataSql(dic);
        uMESProcessResult result = DatabaseHelper.RunProcedureForPage(pageDto);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    private string GetToolProductPrepareDataSql(Dictionary<string, string> dic)
    {
        StringBuilder sql = new StringBuilder();
        sql.AppendLine(@"SELECT f.factoryname,cci.fromworkcentername workcentername,cci.mfgordername,cci.productname,cci.containername,cci.familylistnames,
       DECODE(cci.containertype,7,'单件加工',2,'批量加工',3,'带子序号') containertype,fc.containername facontainername,
       DECODE(cci.containerstatus,1,'在制',2,'关闭') containerstatus,cci.workflowstepname,
       cci.workflowname || ':' || cci.workflowrevision workflowinfo,cci.qty containerqty,m.plannedstartdate mfgstartdate,
       cci.plannedstartdate containerstartdate,m.plannedcompletiondate mfgcompletedate,cci.plannedcompletiondate containercompletedate,
       s.stepno,cci.ExpectMaterialDate,s.description stepInfoDesc,
       ad.starttime,ad.endtime,tl.toolname,tl.description tooldesc,tl.drawingno,tl.tooltype,tl.qty needqty,
       --NVL(tg.menge,0) availableQty,
      -- CASE WHEN NVL(tg.menge,0)>=NVL(tl.qty,0) THEN '是' ELSE '否' END isqitao,
         nvl(rd.resourcename,wk.workstationname) resourcename,nvl(rd.description,wk.workstationname) resourcedescription
FROM apsplaninfo ai 
LEFT JOIN containercurrentinfo cci ON cci.containerid=ai.containerid
LEFT JOIN containersubcontainer csc ON csc.subcontainerid = cci.containerid --查询主制批次
LEFT JOIN container fc ON fc.containerid = csc.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN mfgorder m on m.mfgorderid = cci.mfgorderid
LEFT JOIN apsplandetail2 ad ON ad.apsplaninfoid = ai.apsplaninfoid AND ad.isuse=0
LEFT JOIN stepinfo s ON s.stepinfoid = ad.stepinfoid
LEFT JOIN (
        SELECT  SUM(qty) qty,ct.toolname,ct.description,drawingno,rt.resourcetypename,t.stepinfoid,ct.tooltype
        FROM stepinfotools t
        LEFT JOIN stepinfo sio ON sio.stepinfoid = t.stepinfoid
        LEFT JOIN (
             SELECT specialtoolname toolname,DESCRIPTION,specialtoolid toolid,resourcetypeid,drawingno,'夹模具' tooltype FROM specialtool
             UNION ALL
             SELECT mtt.measuretoolname toolname,mtt.description,mtt.measuretoolid toolid,mtt.resourcetypeid,mtt.drawingno,'量具' tooltype FROM measuretool mtt
             UNION ALL 
             SELECT ctt.cuttoolname toolname,ctt.description,ctt.cuttoolid toolid,ctt.resourcetypeid,ctt.drawingno,'刀具' tooltype FROM cuttool ctt
        )ct ON ct.toolid = t.tools
        LEFT JOIN resourcetype rt ON rt.resourcetypeid = ct.resourcetypeid 
        WHERE ct.toolid IS NOT NULL 
        GROUP BY ct.toolname,ct.description,ct.drawingno,rt.resourcetypename,t.stepinfoid,ct.tooltype
)tl ON tl.stepinfoid = s.stepinfoid
--LEFT JOIN (SELECT SUM(tg.menge) menge,tg.zeinr FROM t_ggdkc tg  GROUP BY tg.zeinr) tg ON tg.zeinr = drawingno
LEFT JOIN resourcedef rd ON rd.resourceid = ad.resourceid
LEFT JOIN Workstation wk ON wk.workstationid=ad.resourceid
WHERE ai.isuse = 0 AND NVL(tl.qty, 0) > 0 AND ad.status != '2'");
        if (dic.ContainsKey("MainFactoryID") && string.IsNullOrWhiteSpace(dic["MainFactoryID"]))
        {
            sql.AppendFormat(" AND fc.originalfactoryid = '{0}'", dic["MainFactoryID"]);
        }
        if (dic.ContainsKey("MfgOrderName") && string.IsNullOrWhiteSpace(dic["MfgOrderName"]))
        {
            sql.AppendFormat(" AND cci.mfgordername = '{0}'", dic["MfgOrderName"]);
        }
        if (dic.ContainsKey("ProductName") && string.IsNullOrWhiteSpace(dic["ProductName"]))
        {
            sql.AppendFormat(" AND cci.productname LIKE '%{0}%'", dic["ProductName"]);
        }
        if (dic.ContainsKey("ContainerName") && string.IsNullOrWhiteSpace(dic["ContainerName"]))
        {
            sql.AppendFormat(" AND cci.ContainerName LIKE '%{0}%'", dic["ContainerName"]);
        }
        if (dic.ContainsKey("StepInfoNo") && string.IsNullOrWhiteSpace(dic["StepInfoNo"]))
        {
            sql.AppendFormat(" AND s.stepno = '{0}'", dic["StepInfoNo"]);
        }
        if (dic.ContainsKey("ApsPlanStartDate1") && string.IsNullOrWhiteSpace(dic["ApsPlanStartDate1"]))
        {
            sql.AppendFormat(" AND ad.starttime >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", dic["ApsPlanStartDate1"]);
        }
        if (dic.ContainsKey("ApsPlanStartDate2") && string.IsNullOrWhiteSpace(dic["ApsPlanStartDate2"]))
        {
            sql.AppendFormat(" AND ad.starttime <= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", dic["ApsPlanStartDate2"]);
        }
        sql.AppendLine(" ORDER BY cci.containername,s.stepseq");
        return sql.ToString();
    }

    /// <summary>
    /// 导出排产池的批次的排产日志
    /// </summary>
    /// <param name="context"></param>
    public void ExportContainerApsLogInfo(HttpContext context)
    {
        if (string.IsNullOrWhiteSpace(context.Request["poolID"].ToString()))
        {
            uMESProcessResult result = new uMESProcessResult();
            result.Result = false;
            result.Message = "请选择排产池";
            context.Response.Write(JsonConvert.SerializeObject(result));
            return;
        }
        string sql = string.Format("SELECT cci.mfgordername \"订单号\", cci.containername \"批次号\", ctl.logmessage \"异常记录\"" +
                         " FROM containercurrentinfo cci " +
                         " INNER JOIN ContainerScheduledTaskLogs ctl on ctl.containerid = cci.containerid and ctl.islatest = 1 and ctl.tasktype = 1 " +
                         " LEFT JOIN apspoolworkcenter aw ON aw.workcenterid = cci.workcenterid " +
                         " LEFT JOIN apspool ao ON ao.apspoolid = aw.apspoolid " +
                         " WHERE cci.containerstatus = 1 AND cci.originalfactoryid = '{0}' AND cci.isinpool = 1 AND cci.isinstore != 1 AND NVL(ao.apspoolid,cci.apspoolid) = '{1}'", rbFactoryID, context.Request["poolID"].ToString());
        DataTable dtRes = DatabaseHelper.Query_ReturnDatTable(sql.ToString());
        byte[] bytes = uLMS_CommonFunction.DataTableExportToExcel(dtRes);
        context.Response.Clear();
        context.Response.Buffer = true;
        context.Response.ClearHeaders();
        context.Response.ClearContent();
        context.Response.ContentType = "application/vnd.ms-excel";
        context.Response.AddHeader("fileDownload", "1");
        context.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}{1}.xls", context.Server.UrlEncode("排产异常导出记录"), DateTime.Now.ToString("yyyyMMddHHmmssffff")));
        context.Response.AddHeader("Content-Length", bytes.Length.ToString());
        context.Response.AddHeader("Content-Type", "UTF-8");
        context.Response.Charset = "UTF-8";
        context.Response.ContentEncoding = Encoding.UTF8;
        context.Response.BinaryWrite(bytes);
        context.Response.Flush();
        context.Response.Close();
    }

    /// <summary>
    /// 获取批次排产信息，以及工步排产明细
    /// </summary>
    /// <param name="context"></param>
    public void GetContainerApsData(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true };
        string containerID = context.Request["containerID"].ToString();
        string stepInfoID = context.Request["stepInfoID"].ToString();
        /*获取批次排产明细*/
        string sql = string.Format(@"SELECT cci.containername,cci.mfgordername,cci.qty,wb.workflowname||':'||w.workflowrevision workflowinfo,
                                    DECODE(cci.containertype,2,'批量加工',3,'批量带子序号','单件单卡') containertypename,m.plannedcompletiondate MFGENDDDATE,m.plannedstartdate MFGSTARTDATE
                                    FROM apsplaninfo ai 
                                    LEFT JOIN containercurrentinfo cci ON cci.containerid = ai.containerid
                                    LEFT JOIN mfgorder m ON m.mfgorderid = cci.mfgorderid
                                    LEFT JOIN workflow w ON w.workflowid = ai.workflowid
                                    LEFT JOIN workflowbase wb ON wb.workflowbaseid = w.workflowbaseid
                                    WHERE ai.containerid = '{0}'", containerID);
        DataTable dtContainer = DatabaseHelper.Query_ReturnDatTable(sql);
        dtContainer.TableName = "dtContainer";
        result.DBDataSet.Tables.Add(dtContainer);
        /*获取工步排产明细，如果不存在工步ID，则查询全部的工步排产信息*/
        sql = string.Format(@"SELECT ap.starttime,ap.endtime,ap.resstarttime,ap.resendtime,r.description resourcedesc,ap.qty,s.makecycle,
                            s.preparecycle,s.transfercycle,s.shipcycle,s.stepno,s.description stepinfodesc
                            FROM apsplandetail2 ap
                            LEFT JOIN resourcedef r ON r.resourceid = ap.resourceid
                            LEFT JOIN stepinfo s ON s.stepinfoid = ap.stepinfoid
                            WHERE ap.containerid = '{0}' AND ap.isuse = 0 {1}", containerID, string.IsNullOrWhiteSpace(stepInfoID) ? "" : string.Format(" AND ap.stepinfoid = '{0}'", stepInfoID));
        DataTable dtStepInfo = DatabaseHelper.Query_ReturnDatTable(sql);
        dtStepInfo.TableName = "dtStepInfo";
        result.DBDataSet.Tables.Add(dtStepInfo);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    /// <summary>
    /// 获取批次排产明细甘特图数据
    /// </summary>
    /// <param name="context"></param>
    public void GetContainerApsDetailGantt(HttpContext context)
    {
        string containerID = context.Request["containerID"].ToString();
        string sql = string.Format(@"SELECT s.stepinfoid,s.description,s.stepno,ap.starttime,ap.endtime,ai.apsplaninfoid,cci.containerid,cci.containername,
                    ai.starttime constarttime,ai.endtime conendtime,ap.apsplandetailid
                    FROM apsplandetail2 ap
                    LEFT JOIN apsplaninfo ai ON ai.apsplaninfoid = ap.apsplaninfoid 
                    LEFT JOIN containercurrentinfo cci ON cci.containerid = ai.containerid
                    LEFT JOIN stepinfo s ON s.stepinfoid = ap.stepinfoid
                    WHERE ai.containerid = '{0}' AND ap.isuse = 0 AND ai.isuse = 0
                    ORDER BY s.stepseq", containerID);
        uMESProcessResult result = new uMESProcessResult();
        DataTable dtCon = DatabaseHelper.Query_ReturnDatTable(sql);
        if (dtCon.Rows.Count == 0)
        {
            result.Message = "没有排产信息";
            context.Response.Write(JsonConvert.SerializeObject(result));
            return;
        }
        Dictionary<string, object> apsData = new Dictionary<string, object>();
        List<Dictionary<string, object>> returnData = new List<Dictionary<string, object>>();
        DataRow drCon = dtCon.Rows[0];
        apsData = new Dictionary<string, object>();
        apsData["id"] = drCon["containerid"].ToString();
        apsData["name"] = drCon["containername"].ToString();
        List<Dictionary<string, object>> conperiods = new List<Dictionary<string, object>>();
        Dictionary<string, object> conPeriod = new Dictionary<string, object>();
        Dictionary<string, object> customData = new Dictionary<string, object>();
        conPeriod["id"] = drCon["apsplaninfoid"].ToString();
        conPeriod["text"] = drCon["containername"].ToString();
        conPeriod["start"] = drCon["constarttime"].ToString();
        conPeriod["end"] = drCon["conendtime"].ToString();
        conPeriod["fill"] = "#64b5f6";
        //个性化属性
        customData = new Dictionary<string, object>();
        customData["apsType"] = "1";
        conPeriod["customData"] = customData;
        conperiods.Add(conPeriod);
        apsData["periods"] = conperiods;
        //批次级个性化属性
        customData = new Dictionary<string, object>();
        customData["containerid"] = drCon["containerid"].ToString();
        apsData["customData"] = customData;
        returnData.Add(apsData);
        foreach (DataRow step in dtCon.Rows)
        {
            Dictionary<string, object> stepApsData = new Dictionary<string, object>();
            stepApsData["id"] = step["stepinfoid"].ToString();
            stepApsData["name"] = step["description"].ToString();
            List<Dictionary<string, object>> periods = new List<Dictionary<string, object>>();
            Dictionary<string, object> period = new Dictionary<string, object>();
            period = new Dictionary<string, object>();
            period["id"] = step["apsplandetailid"].ToString();
            period["text"] = step["description"].ToString();
            period["start"] = step["starttime"].ToString();
            period["end"] = step["endtime"].ToString();
            period["fill"] = "#64b5f6";
            //个性化属性
            customData = new Dictionary<string, object>();
            customData["apsType"] = "1";
            period["customData"] = customData;
            periods.Add(period);
            stepApsData["periods"] = periods;
            stepApsData["parent"] = drCon["containerid"].ToString();
            returnData.Add(stepApsData);
        }
        result.ReturnData = JsonConvert.SerializeObject(returnData);
        result.Result = true;
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    /// <summary>
    /// 初始化热表滚动制造计划页面数据
    /// </summary>
    /// <param name="context"></param>
    public void InitRbApsLimitPageData(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true };
        //获取分厂
        DataTable dtFactory = m_CommonObj.GetFactory(new Dictionary<string, string>());
        dtFactory.TableName = "dtFactory";
        result.DBDataSet.Tables.Add(dtFactory);
        //获取排产池
        Dictionary<string, string> para = new Dictionary<string, string>();
        para["FactoryID"] = m_UserInfo.FactoryID;
        para["EmployeeID"] = m_UserInfo.EmployeeID.ToString();
        DataTable dtPool = m_LMObj.GetPoolList(para);
        dtPool.TableName = "dtPool";
        result.DBDataSet.Tables.Add(dtPool);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    /// <summary>
    /// 获取可排产批次数据
    /// </summary>
    /// <param name="context"></param>
    public void GetApsPlanContainerData(HttpContext context)
    {
        Dictionary<string, string> dic = JsonConvert.DeserializeObject<Dictionary<string, string>>(context.Request["params"].ToString());
        dic.Add("IsPaging", "true");//分页
        dic.Add("IsApsSended", "1");//排除排产已下发的数据
        uMESProcessResult result = rbApsObj.GetApsPlanContainerData(dic);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    /// <summary>
    /// 计算排产池批次的优先级（顺序号）
    /// </summary>
    /// <param name="context"></param>
    public void CalcContainerApsPriority(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true };
        if (string.IsNullOrWhiteSpace(context.Request["poolID"].ToString()))
        {
            result.Result = false;
            result.Message = "未选择排产池";
            context.Response.Write(JsonConvert.SerializeObject(result));
            return;
        }
        string msg = string.Empty;
        Dictionary<string, string> dic = new Dictionary<string, string>();
        dic.Add("IsPaging", "false"); //不分页
        dic.Add("IsAps", "1"); //排除已经排产的数据
        dic.Add("PoolID", context.Request["poolID"].ToString()); //排产池ID
        result.Result = rbApsObj.CalcContainerApsPriority(dic, ref msg);
        result.Message = msg;
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    /// <summary>
    /// 排产计算
    /// </summary>
    /// <param name="context"></param>
    public void CalcContainerApsPlan(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true };
        DataTable dtContainer = JsonConvert.DeserializeObject<DataTable>(context.Request.Form["params"].ToString());
        if (dtContainer.Rows.Count == 0)
        {
            result.Result = false;
            result.Message = "未选择批次";
            context.Response.Write(JsonConvert.SerializeObject(result));
            return;
        }
        string sql = "SELECT 1 FROM apsplandetail ap WHERE ap.containerid = '{0}'";
        foreach (DataRow item in dtContainer.Rows)
        {
            if (DatabaseHelper.GetSingle(string.Format(sql, item["containerid"].ToString())) != null)
            {
                result.Result = false;
                result.Message = "选择的批次存在工序排产，无法工步排产";
                break;
            }
        }
        if (result.Result)
        {
            result = rbApsObj.CalcContainersApsPlan(dtContainer, m_UserInfo);
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    /// <summary>
    /// 保存手动指定日期
    /// </summary>
    /// <param name="context"></param>
    public void SaveManualStartDate(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true, Message = "修改成功" };
        DataTable containerDatas = JsonConvert.DeserializeObject<DataTable>(context.Request["containerDatas"].ToString());//需要更改的批次
        string manualStartDate = context.Request["manualStartDate"].ToString();//手动指定的时间
        if (!string.IsNullOrWhiteSpace(manualStartDate) && DateTime.Parse(manualStartDate) < DateTime.Now)
        {
            result.Result = false;
            result.Message = "指定日期不得小于系统当前时间";
            context.Response.Write(JsonConvert.SerializeObject(result));
            return;
        }
        if (containerDatas.Rows.Count == 0)
        {
            result.Result = false;
            result.Message = "未选择批次";
            context.Response.Write(JsonConvert.SerializeObject(result));
            return;
        }
        List<ExcuteEntity> excuteList = new List<ExcuteEntity>();
        foreach (DataRow item in containerDatas.Rows)
        {
            ExcuteEntity exuteEntity = new ExcuteEntity("containercurrentinfo", ExcuteEntity.ExcuteTypeEnum.update);
            exuteEntity.ExcuteFileds = new List<FieldEntity>() { new FieldEntity("ExpectMaterialDate", string.IsNullOrWhiteSpace(manualStartDate) ? (DateTime?)null : DateTime.Parse(manualStartDate), FieldEntity.FieldTypeEnum.DateTime) };
            exuteEntity.WhereFileds = new List<FieldEntity>() { new FieldEntity("containerid", item["containerid"].ToString(), FieldEntity.FieldTypeEnum.Str) };
            excuteList.Add(exuteEntity);
        }
        DatabaseHelper.ExecuteDataByEntitys(excuteList);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}